
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Prueba {

    static Connection conn;

    private static String HOST = "10.10.1.80";
    private static String USER = "eprice";
    private static String PASSWD = "eprice";

    public static void main(String args[]) throws Exception {
        Class.forName("com.inet.tds.TdsDriver");
        conn = DriverManager.getConnection("jdbc:inetdae7:"+HOST, USER, PASSWD );
        Map mapa = new HashMap();
        mapa.put("host", "10.15.1.33");
        mapa.put("mailAut", "appMail");
        mapa.put("mailFrom", "EPrice");
        mapa.put("passwordFrom", "Sistemas06");
        System.out.println( enviaCorreo(conn.createStatement(), mapa, "1") );
    }
    
    public static String enviaCorreo(Statement stmt, Map map_param, String str_idSol) throws SQLException, InstantiationException, IllegalAccessException {
        String str_retVal = "";
        stmt = (stmt.getConnection()).createStatement(ResultSet.
                TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet rs;
        KC.utils.Mail mail = new KC.utils.Mail();
        mail.setAuthentication(true);
        mail.setMailAut((String)map_param.get("mailAut"));
        mail.setPasswordFrom((String)map_param.get("passwordFrom"));
        mail.setMailFrom((String)map_param.get("mailFrom"));
        mail.setHost((String)map_param.get("host"));

        mail.setSubject("Solicitud de Autorizacion de Precio");

        String str_body = "";
        String str_idEstatus = "";
        String str_codigoCli = "";
        String str_descCli = "";
        String str_codigoProd = "";
        String str_descProd = "";
        String str_descPerfil = "";
        String str_idDirigido = "";
        String str_email = "";

        String str_query =
                " SELECT cs.id_estatus,                                                " +
                "        cs.codigo_cli,                                                " +
                "        vc.descripcion_cli,                                           " +
                "        cs.codigo_pro,                                                " +
                "        vp.descripcion_prod,                                          " +
                "        p.desc_perfil,                                                " +
                "        mc.id_dirigidoCorreo,                                         " +
                "        CASE                                                          " +
                "            WHEN desc_perfil = 'VPO' THEN email_vpo                   " +
                "            WHEN desc_perfil = 'Gerente' THEN email_gerente           " +
                "            WHEN desc_perfil = 'Ejecutivo' THEN email_solicitante     " +
                "            WHEN desc_perfil = 'Coordinador' THEN email_coordinadora  " +
                "            WHEN desc_perfil = 'Director' THEN email_director         " +
                "        END email                                                     " +
                " FROM ep_vw_correosSolicitud cs                                       " +
                "      INNER JOIN ep_matrizCorreo mc                                   " +
                "              ON cs.id_estatus = mc.id_estatus                        " +
                "      INNER JOIN ep_vw_perfil p                                       " +
                "              ON mc.id_perfil = p.id_perfil                           " +
                "      INNER JOIN  ep_vw_producto vp                                   " +
                "              ON  cs.codigo_pro = vp.codigo_prod                      " +
                "      INNER JOIN  ep_vw_cliente vc                                    " +
                "              ON  cs.codigo_cli = vc.codigo_cli                       " +
                "       WHERE id_sol = '" + str_idSol + "                              " +
                "'                                                                     " +
                " ORDER BY id_dirigidoCorreo,  email                                   ";

        rs = stmt.executeQuery(str_query);
        while (rs.next()) {
            str_idEstatus = rs.getString("id_estatus");
            str_codigoCli = rs.getString("codigo_cli");
            str_descCli = rs.getString("descripcion_cli");
            str_codigoProd = rs.getString("codigo_pro");
            str_descProd = rs.getString("descripcion_prod");
            str_descPerfil = rs.getString("desc_perfil");
            str_idDirigido = rs.getString("id_dirigidoCorreo");
            str_email = rs.getString("email")==null?"":rs.getString("email");

            str_body += "Se ha Creado una solicitud " + str_codigoProd + " " +
                    str_descProd + " " + str_codigoCli + " " + str_descCli + "";

            if (str_idDirigido.equals("1")){
                System.out.println("A:");
                System.out.println(str_email);
                mail.addMailTo("jdelgado@connet.net.mx");
            }else{
                System.out.println("CC:");
                System.out.println(str_email);
                mail.addMailCC("josejc06@hotmail.com");
            }
            if (rs.next()) {
                String str_mailNext = rs.getString("email")==null?"":rs.getString("email");
                if (!str_email.equals(str_mailNext)){
                    mail.setBody(str_body);
                    str_retVal = mail.sendMail();
                    System.out.println("Enviando a " + str_email);
                    System.out.println("Mensaje " + str_body);
                    str_body = "";

                } else{
                    str_body += "\n";
                }
                rs.previous();
            } else {
                mail.setBody(str_body);
                mail.sendMail();
                System.out.println("Enviando a " + str_email);
                System.out.println("Mensaje " + str_body);
            }
        }
        return str_retVal;
    }

}